﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ComponentModel;
using System.Data;
using System.Transactions;


using Global.Class.Library;
using DataTransferObject;
using DataAccessLayer;
using DataAccessLayer.MarketingIncentiveDTSTableAdapters;


namespace BusinessLogicLayer
{
    public class MarketingIncentiveBLL : NotifyPropertyChangeObject
    {
        public GlobalEnum.TaskID TaskID { get { return GlobalEnum.TaskID.MarketingIncentive; } }

        private UserInformation userOrganization;

        private MarketingIncentiveMaster marketingIncentiveMaster;

        private BindingList<MarketingIncentiveDetail> marketingIncentiveDetailList;



        public MarketingIncentiveBLL()
        {
            try
            {
                if (GlobalVariables.shouldRestoreProcedure) RestoreProcedure();

                userOrganization = new UserInformation();

                marketingIncentiveMaster = new MarketingIncentiveMaster();

                this.marketingIncentiveDetailList = new BindingList<MarketingIncentiveDetail>();

                GlobalDefaultValue.Apply(this);


                this.MarketingIncentiveMaster.PropertyChanged += new PropertyChangedEventHandler(MarketingIncentiveMaster_PropertyChanged);

                this.MarketingIncentiveDetailList.ListChanged += new ListChangedEventHandler(MarketingIncentiveDetail_ListChanged);

            }
            catch (Exception exception)
            {
                throw exception;
            }
        }

        private void MarketingIncentiveMaster_PropertyChanged(object sender, PropertyChangedEventArgs e)
        {
            this.SetDirty();
        }

        private void MarketingIncentiveDetail_ListChanged(object sender, ListChangedEventArgs e)
        {
            this.SetDirty();
        }




        #region <Adapter>

        private MarketingIncentiveListingTableAdapter listingTableAdapter;
        protected MarketingIncentiveListingTableAdapter ListingTableAdapter
        {
            get
            {
                if (listingTableAdapter == null) listingTableAdapter = new MarketingIncentiveListingTableAdapter();
                return listingTableAdapter;
            }
        }

        private MarketingIncentiveMasterTableAdapter masterTableAdapter;
        protected MarketingIncentiveMasterTableAdapter MasterTableAdapter
        {
            get
            {
                if (masterTableAdapter == null) masterTableAdapter = new MarketingIncentiveMasterTableAdapter();
                return masterTableAdapter;
            }
        }

        MarketingIncentiveDetailTableAdapter marketingIncentiveDetailTableAdapter;
        protected MarketingIncentiveDetailTableAdapter MarketingIncentiveDetailTableAdapter
        {
            get
            {
                if (marketingIncentiveDetailTableAdapter == null) marketingIncentiveDetailTableAdapter = new MarketingIncentiveDetailTableAdapter();
                return marketingIncentiveDetailTableAdapter;
            }
        }


        #endregion <Adapter>

        #region <Storage>

        public UserInformation UserOrganization
        {
            get { return this.userOrganization; }
            set { this.userOrganization = value; }
        }

        public MarketingIncentiveDTS.MarketingIncentiveListingDataTable MarketingIncentiveMasterList(DateTime lowerFillterDate, DateTime upperFillterDate)
        {
            return this.ListingTableAdapter.GetData(lowerFillterDate, upperFillterDate);
        }

        public MarketingIncentiveMaster MarketingIncentiveMaster
        {
            get { return this.marketingIncentiveMaster; }
        }

        public BindingList<MarketingIncentiveDetail> MarketingIncentiveDetailList
        {
            get { return this.marketingIncentiveDetailList; }
        }


        #endregion <Storage>

        #region Properties

        #region <Primary Key>

        public int MarketingIncentiveID   //Primary Key
        {
            get { return this.MarketingIncentiveMaster.MarketingIncentiveID; }
            private set
            {
                if (this.MarketingIncentiveMaster.MarketingIncentiveID != value)
                {
                    this.StopTracking();

                    this.MarketingIncentiveMaster.MarketingIncentiveID = value;

                    this.MarketingIncentiveGetMaster();
                    this.MarketingIncentiveGetDetail();

                    this.StartTracking();
                    this.Reset();
                }

            }
        }

        #endregion <Primary Key>

        private void MarketingIncentiveGetMaster()
        {
            if (this.MarketingIncentiveID > 0)
            {
                MarketingIncentiveDTS.MarketingIncentiveMasterDataTable masterDataTable = this.MasterTableAdapter.GetData(this.MarketingIncentiveID);

                if (masterDataTable.Count > 0)
                {
                    this.MarketingIncentiveMaster.StopTracking();

                    this.MarketingIncentiveMaster.MarketingProgramID = masterDataTable[0].MarketingProgramID;
                    this.MarketingIncentiveMaster.MarketingPaymentTypeID = masterDataTable[0].MarketingPaymentTypeID;
                    this.MarketingIncentiveMaster.RequestedDate = masterDataTable[0].RequestedDate;
                    this.MarketingIncentiveMaster.EntryDate = masterDataTable[0].EntryDate;
                    this.MarketingIncentiveMaster.PaymentPeriod = masterDataTable[0].PaymentPeriod;
                    this.MarketingIncentiveMaster.PaymentMachanics = masterDataTable[0].PaymentMachanics;
                    this.MarketingIncentiveMaster.RequestedEmployeeID = masterDataTable[0].RequestedEmployeeID;
                    this.MarketingIncentiveMaster.NotedEmployeeID = masterDataTable[0].NotedEmployeeID;
                    this.MarketingIncentiveMaster.ApprovedEmployeeID = masterDataTable[0].ApprovedEmployeeID;
                    this.MarketingIncentiveMaster.Remarks = masterDataTable[0].Remarks;

                    this.MarketingIncentiveMaster.StartTracking();

                    this.MarketingIncentiveMaster.Reset();

                    this.UserOrganization.UserID = masterDataTable[0].UserID;
                    this.UserOrganization.UserOrganizationID = masterDataTable[0].UserOrganizationID;
                }
                else throw new System.ArgumentException("Insufficient get data");
            }
            else
            {
                GlobalDefaultValue.Apply(this.MarketingIncentiveMaster);
                this.MarketingIncentiveMaster.RequestedDate = DateTime.Today;
                this.MarketingIncentiveMaster.EntryDate = DateTime.Today;
                this.MarketingIncentiveMaster.Reset();
            }
        }


        private void MarketingIncentiveGetDetail()
        {
            this.marketingIncentiveDetailList.RaiseListChangedEvents = false;
            this.marketingIncentiveDetailList.Clear();
            if (this.MarketingIncentiveID > 0)
            {
                MarketingIncentiveDTS.MarketingIncentiveDetailDataTable marketingIncentiveDetailDataTable = this.MarketingIncentiveDetailTableAdapter.GetData(this.MarketingIncentiveID);

                if (marketingIncentiveDetailDataTable.Count > 0)
                {
                    foreach (MarketingIncentiveDTS.MarketingIncentiveDetailRow marketingIncentiveDetailRow in marketingIncentiveDetailDataTable.Rows)
                    {
                        this.marketingIncentiveDetailList.Add(new MarketingIncentiveDetail(marketingIncentiveDetailRow.MarketingIncentiveID, marketingIncentiveDetailRow.SerialID, marketingIncentiveDetailRow.CustomerID, marketingIncentiveDetailRow.MarketingPaymentTermID, marketingIncentiveDetailRow.TurnOverAmount, marketingIncentiveDetailRow.IncentiveAmount, marketingIncentiveDetailRow.Description, marketingIncentiveDetailRow.Remarks));
                    }
                }
            }
            this.marketingIncentiveDetailList.RaiseListChangedEvents = true;
            this.marketingIncentiveDetailList.ResetBindings();
        }



        #endregion Properties





        #region Object Permission

        public override bool IsValid
        {
            get
            {
                List<MarketingIncentiveDetail> inValidMarketingIncentiveDetail = this.MarketingIncentiveDetailList.Where(marketingIncentiveDetail => !marketingIncentiveDetail.IsValid).ToList();
                return this.MarketingIncentiveMaster.IsValid && inValidMarketingIncentiveDetail.Count == 0;
            }
        }


        public bool ReadOnly
        {
            get
            {
                try
                {
                    return GlobalUserPermission.GetUserReadOnly(GlobalVariables.GlobalUserInformation.UserID, this.TaskID);
                }
                catch
                {
                    return true;
                }
            }
        }

        public bool Editable
        {
            get
            {
                try
                {
                    if (this.marketingIncentiveMaster.RequestedDate <= GlobalUserPermission.GlobalLockedDate()) return false;

                    if (!GlobalUserPermission.GetUserEditable(GlobalVariables.GlobalUserInformation.UserID, this.TaskID, this.UserOrganization.UserOrganizationID)) return false;

                    if (!GlobalUserPermission.GetEditable("SPMarketingIncentiveApproved", this.MarketingIncentiveID)) return false;

                    return GlobalUserPermission.GetEditable("SPMarketingIncentiveEditable", this.MarketingIncentiveID);
                }
                catch
                {
                    return false;
                }
            }
        }

        public bool Verifiable
        {
            get
            {
                try
                {
                    if (this.marketingIncentiveMaster.RequestedDate <= GlobalUserPermission.GlobalLockedDate()) return false;

                    if (!GlobalUserPermission.GetUserEditable(GlobalVariables.GlobalUserInformation.UserID, GlobalEnum.TaskID.MarketingIncentiveVerifiable, this.UserOrganization.UserOrganizationID)) return false;

                    return GlobalUserPermission.GetEditable("SPMarketingIncentiveEditable", this.MarketingIncentiveID);
                }
                catch
                {
                    return false;
                }
            }
        }

        public bool Unverifiable
        {
            get
            {
                try
                {
                    if (this.marketingIncentiveMaster.RequestedDate <= GlobalUserPermission.GlobalLockedDate()) return false;

                    if (!GlobalUserPermission.GetUserEditable(GlobalVariables.GlobalUserInformation.UserID, GlobalEnum.TaskID.MarketingIncentiveUnverifiable, this.UserOrganization.UserOrganizationID)) return false;

                    return GlobalUserPermission.GetEditable("SPMarketingIncentiveEditable", this.MarketingIncentiveID);
                }
                catch
                {
                    return false;
                }
            }
        }


        #endregion Object Permission






        public void Fill(int marketingIncentiveID)
        {
            if (this.MarketingIncentiveID == marketingIncentiveID) this.MarketingIncentiveID = -1; //Enforce to reload
            this.MarketingIncentiveID = marketingIncentiveID;
        }

        public void New()
        {
            if (this.MarketingIncentiveID == 0) this.MarketingIncentiveID = -1;
            this.MarketingIncentiveID = 0;
        }

        public void Edit()
        {

        }

        #region Save & Delete Method

        public bool MarketingIncentiveDetailCustomUpdate(MarketingIncentiveDetail marketingIncentiveDetail, double turnOverAmount, double incentiveAmount, string description, string remarks)
        {
            try
            {
                if (this.MarketingIncentiveID != marketingIncentiveDetail.MarketingIncentiveID) throw new System.ArgumentException("Insufficient update! Conflict parameter!", "Custom update");

                using (TransactionScope transactionScope = new TransactionScope())
                {
                    if (this.MarketingIncentiveDetailTableAdapter.MarketingIncentiveDetailCustomUpdate(marketingIncentiveDetail.MarketingIncentiveID, marketingIncentiveDetail.SerialID, turnOverAmount, incentiveAmount, description, remarks, this.MarketingIncentiveMaster.RequestedDate, this.MarketingIncentiveMaster.MarketingProgramID, this.MarketingIncentiveMaster.MarketingPaymentTypeID, marketingIncentiveDetail.CustomerID, marketingIncentiveDetail.MarketingPaymentTermID, this.MarketingIncentiveMaster.PaymentPeriod, marketingIncentiveDetail.TurnOverAmount, marketingIncentiveDetail.IncentiveAmount, marketingIncentiveDetail.Description, marketingIncentiveDetail.Remarks) != 1) throw new System.ArgumentException("Insufficient update! Please check your data and try again. Thank you!", "Custom update");

                    transactionScope.Complete();
                }

                this.Fill(this.MarketingIncentiveID);
                return true;
            }
            catch (System.Exception exception)
            {
                throw exception;
            }
        }


        public bool Save()
        {
            int marketingIncentiveID = 0;

            try
            {

                if (!this.SaveValidate()) throw new System.ArgumentException("Insufficient save", "Save validate");

                using (TransactionScope transactionScope = new TransactionScope())
                {
                    if (!this.Editable) throw new System.ArgumentException("Insufficient save", "Uneditable");

                    if (!this.SaveMaster(ref marketingIncentiveID)) throw new System.ArgumentException("Insufficient save", "Save master");

                    if (!this.SaveDetail(marketingIncentiveID)) throw new System.ArgumentException("Insufficient save", "Save detail");

                    if (this.SaveConflict()) throw new System.ArgumentException("Insufficient save", "Save conflict");

                    transactionScope.Complete();                    //northwindDataSet.AcceptChanges();
                }

                this.Fill(marketingIncentiveID);
                return true;
            }
            catch (System.Exception exception)
            {
                throw exception;
            }
        }


        public bool Delete()
        {
            if (this.marketingIncentiveMaster.MarketingIncentiveID <= 0) return false;

            try
            {

                if (!this.SaveValidate()) throw new System.ArgumentException("Insufficient delete", "Delete validate");

                using (TransactionScope transactionScope = new TransactionScope())
                {
                    if (!this.Editable) throw new System.ArgumentException("Insufficient delete", "Uneditable");

                    if (!this.SaveUndo(this.marketingIncentiveMaster.MarketingIncentiveID)) throw new System.ArgumentException("Insufficient delete", "Delete detail");

                    if (this.MasterTableAdapter.Delete(this.marketingIncentiveMaster.MarketingIncentiveID) != 1) throw new System.ArgumentException("Insufficient delete", "Delete master");

                    if (this.SaveConflict()) throw new System.ArgumentException("Insufficient delete", "Delete conflict");

                    transactionScope.Complete();                    //northwindDataSet.AcceptChanges();
                }
                this.Fill(0);
                return true;
            }
            catch (System.Exception exception)
            {
                throw exception;
            }
        }


        private bool SaveValidate()
        {
            ExceptionTable exceptionTable = new ExceptionTable(new string[2, 2] { { "ExceptionCategory", "System.String" }, { "ExceptionDescription", "System.String" } });

            this.UserOrganization = GlobalUserPermission.GetUserInformation(this.MarketingIncentiveMaster.RequestedEmployeeID, this.MarketingIncentiveMaster.RequestedDate);

            if (this.UserOrganization.UserID <= 0 || this.UserOrganization.UserOrganizationID <= 0) exceptionTable.AddException(new string[] { GlobalVariables.stringFieldRequired, "User information" });


            if (exceptionTable.Table.Rows.Count <= 0 && this.IsValid) return true; else throw new CustomException("Save validate", exceptionTable.Table);
        }


        private bool SaveMaster(ref int marketingIncentiveID)
        {
            MarketingIncentiveDTS.MarketingIncentiveMasterDataTable masterDataTable;
            MarketingIncentiveDTS.MarketingIncentiveMasterRow masterRow;

            if (this.marketingIncentiveMaster.MarketingIncentiveID <= 0) //Add
            {
                masterDataTable = new MarketingIncentiveDTS.MarketingIncentiveMasterDataTable();
                masterRow = masterDataTable.NewMarketingIncentiveMasterRow();
            }
            else //Edit
            {
                if (!this.SaveUndo(marketingIncentiveMaster.MarketingIncentiveID)) throw new System.ArgumentException("Insufficient save", "Save undo");
                masterDataTable = this.MasterTableAdapter.GetData(marketingIncentiveMaster.MarketingIncentiveID);
                if (masterDataTable.Count > 0) masterRow = masterDataTable[0]; else throw new System.ArgumentException("Insufficient save", "Get for edit");
            }

            masterRow.MarketingProgramID = this.marketingIncentiveMaster.MarketingProgramID;
            masterRow.MarketingPaymentTypeID = this.marketingIncentiveMaster.MarketingPaymentTypeID;
            masterRow.RequestedDate = this.marketingIncentiveMaster.RequestedDate;
            masterRow.EntryDate = this.marketingIncentiveMaster.EntryDate;
            masterRow.PaymentPeriod = this.marketingIncentiveMaster.PaymentPeriod;
            masterRow.PaymentMachanics = this.marketingIncentiveMaster.PaymentMachanics;
            masterRow.RequestedEmployeeID = this.marketingIncentiveMaster.RequestedEmployeeID;
            masterRow.NotedEmployeeID = this.marketingIncentiveMaster.NotedEmployeeID;
            masterRow.ApprovedEmployeeID = this.marketingIncentiveMaster.ApprovedEmployeeID;
            masterRow.Remarks = this.marketingIncentiveMaster.Remarks;

            masterRow.UserID = this.UserOrganization.UserID;
            masterRow.UserOrganizationID = this.UserOrganization.UserOrganizationID;

            if (this.marketingIncentiveMaster.MarketingIncentiveID <= 0) masterDataTable.AddMarketingIncentiveMasterRow(masterRow);

            int rowsAffected = this.MasterTableAdapter.Update(masterRow);

            marketingIncentiveID = masterRow.MarketingIncentiveID;

            return rowsAffected == 1;

        }


        private bool SaveDetail(int marketingIncentiveID)
        {
            int serialID = 0; int rowsAffected = 0;


            #region <Item Category>

            serialID = 0;

            MarketingIncentiveDTS.MarketingIncentiveDetailDataTable marketingIncentiveDetailDataTable = new MarketingIncentiveDTS.MarketingIncentiveDetailDataTable();

            foreach (MarketingIncentiveDetail marketingIncentiveDetail in this.marketingIncentiveDetailList)
            {
                MarketingIncentiveDTS.MarketingIncentiveDetailRow marketingIncentiveDetailRow = marketingIncentiveDetailDataTable.NewMarketingIncentiveDetailRow();

                marketingIncentiveDetailRow.MarketingIncentiveID = marketingIncentiveID;
                marketingIncentiveDetailRow.SerialID = ++serialID;

                marketingIncentiveDetailRow.CustomerID = marketingIncentiveDetail.CustomerID;
                marketingIncentiveDetailRow.MarketingProgramID = this.MarketingIncentiveMaster.MarketingProgramID;
                marketingIncentiveDetailRow.MarketingPaymentTypeID = this.MarketingIncentiveMaster.MarketingPaymentTypeID;
                marketingIncentiveDetailRow.MarketingPaymentTermID = marketingIncentiveDetail.MarketingPaymentTermID;
                marketingIncentiveDetailRow.RequestedDate = this.MarketingIncentiveMaster.RequestedDate;
                marketingIncentiveDetailRow.PaymentPeriod = this.MarketingIncentiveMaster.PaymentPeriod;
                marketingIncentiveDetailRow.TurnOverAmount = marketingIncentiveDetail.TurnOverAmount;
                marketingIncentiveDetailRow.IncentiveAmount = marketingIncentiveDetail.IncentiveAmount;
                marketingIncentiveDetailRow.PaidAmount = 0;
                marketingIncentiveDetailRow.Description = marketingIncentiveDetail.Description;
                marketingIncentiveDetailRow.Remarks = marketingIncentiveDetail.Remarks;

                marketingIncentiveDetailDataTable.AddMarketingIncentiveDetailRow(marketingIncentiveDetailRow);
            }

            rowsAffected = this.MarketingIncentiveDetailTableAdapter.Update(marketingIncentiveDetailDataTable);
            if (rowsAffected != this.marketingIncentiveDetailList.Count) throw new System.ArgumentException("Insufficient save", "Save detail: Item Category");


            #endregion <Item Category>


            return true;
        }


        private bool SaveUndo(int marketingIncentiveID)
        {
            this.MarketingIncentiveDetailTableAdapter.Delete(marketingIncentiveID);

            return true;
        }


        private bool SaveConflict()
        {
            return false;
        }



        #endregion



        #region Import Excel

        public bool ImportExcel(string fileName)
        {
            try
            {
                DateTime dateTimeValue; string marketingProgramReference = "";
                ExceptionTable exceptionTable = new ExceptionTable(new string[2, 2] { { "ExceptionCategory", "System.String" }, { "ExceptionDescription", "System.String" } });

                TimeSpan timeout = TimeSpan.FromMinutes(90);
                using (TransactionScope transactionScope = new TransactionScope(TransactionScopeOption.Required, timeout))
                {
                    if (this.ReadOnly) throw new System.ArgumentException("Import", "Permission conflict");

                    DataTable excelDataTable = OleDbDatabase.OpenExcelSheet(OleDbDatabase.MappingTaskID.MarketingIncentive, fileName);
                    if (excelDataTable != null && excelDataTable.Rows.Count > 0)
                    {
                        string importMarketingProgramID = "";
                        MarketingIncentiveBLL importMarketingIncentiveBLL = new MarketingIncentiveBLL();

                        foreach (DataRow excelDataRow in excelDataTable.Rows)
                        {
                            if (importMarketingProgramID != excelDataRow["MarketingProgramID"].ToString().Trim())
                            {//Start a new importMarketingIncentiveBLL
                                importMarketingProgramID = excelDataRow["MarketingProgramID"].ToString().Trim();

                                //Save current importMarketingIncentiveBLL if existing
                                if (!exceptionTable.IsDirty)
                                    if (importMarketingIncentiveBLL.IsDirty && !importMarketingIncentiveBLL.Save())
                                        exceptionTable.AddException(new string[] { "Insufficient save", marketingProgramReference });

                                //Start a new importMarketingIncentiveBLL
                                importMarketingIncentiveBLL.New();
                                exceptionTable.ClearDirty();

                                ERmgrUIP.SPGetMarketingProgramRow marketingProgramRow = CommonDatabaseMethod.GetMarketingProgram(0, excelDataRow["MarketingProgramID"].ToString().Trim());
                                if (marketingProgramRow != null)
                                {
                                    importMarketingIncentiveBLL.marketingIncentiveMaster.MarketingProgramID = marketingProgramRow.MarketingProgramID; marketingProgramReference = excelDataRow["MarketingProgramID"].ToString().Trim();
                                }
                                else
                                {//Handle exception when MarketingProgram can not be found
                                    exceptionTable.AddException(new string[] { "Marketing program", excelDataRow["MarketingProgramID"].ToString() });
                                }


                                ERmgrUIP.ListMarketingPaymentTypeRow marketingPaymentTypeRow = CommonDatabaseMethod.GetMarketingPaymentType(0, excelDataRow["MarketingPaymentTypeID"].ToString().Trim());
                                if (marketingPaymentTypeRow != null)
                                    importMarketingIncentiveBLL.marketingIncentiveMaster.MarketingPaymentTypeID = marketingPaymentTypeRow.MarketingPaymentTypeID;
                                else
                                {//Handle exception when MarketingPaymentType can not be found
                                    exceptionTable.AddException(new string[] { "Payment Type", excelDataRow["MarketingPaymentTypeID"].ToString() });
                                }


                                if (DateTime.TryParse(excelDataRow["RequestedDate"].ToString(), out dateTimeValue)) importMarketingIncentiveBLL.marketingIncentiveMaster.RequestedDate = dateTimeValue; else exceptionTable.AddException(new string[] { "Invalid Date Value", excelDataRow["RequestedDate"].ToString() });
                                if (DateTime.TryParse(excelDataRow["EntryDate"].ToString(), out dateTimeValue)) importMarketingIncentiveBLL.marketingIncentiveMaster.EntryDate = dateTimeValue; else exceptionTable.AddException(new string[] { "Invalid Date Value", excelDataRow["EntryDate"].ToString() });

                                importMarketingIncentiveBLL.marketingIncentiveMaster.PaymentPeriod = excelDataRow["PaymentPeriod"].ToString();
                                importMarketingIncentiveBLL.marketingIncentiveMaster.PaymentMachanics = excelDataRow["PaymentMachanics"].ToString();

                                ERmgrUIP.ListStaffNameRow listStaffNameRow = CommonDatabaseMethod.GetStaffName(0, excelDataRow["RequestedEmployeeID"].ToString().Trim());
                                if (listStaffNameRow != null)
                                    importMarketingIncentiveBLL.marketingIncentiveMaster.RequestedEmployeeID = listStaffNameRow.StaffID;
                                else
                                {//Handle exception when ItemType can not be found
                                    exceptionTable.AddException(new string[] { "Staffs", excelDataRow["RequestedEmployeeID"].ToString() });
                                }

                                listStaffNameRow = CommonDatabaseMethod.GetStaffName(0, excelDataRow["NotedEmployeeID"].ToString().Trim());
                                if (listStaffNameRow != null)
                                    importMarketingIncentiveBLL.marketingIncentiveMaster.NotedEmployeeID = listStaffNameRow.StaffID;
                                else
                                {//Handle exception when ItemType can not be found
                                    exceptionTable.AddException(new string[] { "Staffs", excelDataRow["NotedEmployeeID"].ToString() });
                                }

                                listStaffNameRow = CommonDatabaseMethod.GetStaffName(0, excelDataRow["ApprovedEmployeeID"].ToString().Trim());
                                if (listStaffNameRow != null)
                                    importMarketingIncentiveBLL.marketingIncentiveMaster.ApprovedEmployeeID = listStaffNameRow.StaffID;
                                else
                                {//Handle exception when ItemType can not be found
                                    exceptionTable.AddException(new string[] { "Staffs", excelDataRow["ApprovedEmployeeID"].ToString() });
                                }

                                importMarketingIncentiveBLL.marketingIncentiveMaster.Remarks = excelDataRow["Remarks"].ToString();
                            }

                            #region Import Detail Collection

                            ERmgrUIP.SPGetCustomerNameRow customerNameRow = CommonDatabaseMethod.GetCustomerName(0, excelDataRow["CustomerID"].ToString().Trim());
                            if (customerNameRow != null)
                            {
                                ERmgrUIP.SPGetMarketingPaymentTermRow marketingPaymentTermRow = CommonDatabaseMethod.GetMarketingPaymentTerm(0, excelDataRow["MarketingPaymentTermID"].ToString().Trim());
                                if (marketingPaymentTermRow != null)
                                    importMarketingIncentiveBLL.MarketingIncentiveDetailList.Add(new MarketingIncentiveDetail(-1, -1, customerNameRow.CustomerID, marketingPaymentTermRow.MarketingPaymentTermID, (double)excelDataRow["TurnOverAmount"], (double)excelDataRow["IncentiveAmount"], excelDataRow["DetailDescription"].ToString().Trim(), excelDataRow["DetailRemarks"].ToString().Trim()));
                                else
                                {//Handle exception when MarketingPaymentTerm can not be found
                                    exceptionTable.AddException(new string[] { "Payment Term", excelDataRow["MarketingPaymentTermID"].ToString() });
                                }
                            }

                            else
                            {//Handle exception when CustomerName can not be found
                                exceptionTable.AddException(new string[] { "Customer", excelDataRow["CustomerID"].ToString() });
                            }

                            #endregion Import Detail Collection

                        }

                        //Save the last importMarketingIncentiveBLL after loop for all excelDataRow
                        if (!exceptionTable.IsDirty)
                            if (importMarketingIncentiveBLL.IsDirty && !importMarketingIncentiveBLL.Save())
                                exceptionTable.AddException(new string[] { "Insufficient save", marketingProgramReference });
                        //--------------OK

                    }
                    if (exceptionTable.Table.Rows.Count <= 0)
                        transactionScope.Complete();
                    else
                        throw new CustomException("Invalid data value or data entry not found", exceptionTable.Table);

                }
                return true;
            }
            catch (System.Exception exception)
            {
                throw exception;
            }

        }

        #endregion Import Excel



        private void RestoreProcedure()
        {
            string queryString;
            string[] queryArray;


            queryString = "     @LowerFillterDate DateTime, @UpperFillterDate DateTime " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       SELECT      LEFT(CONVERT(char(10), MarketingIncentiveMaster.RequestedDate, 111), 7) AS RequestedMonth, MarketingIncentiveMaster.MarketingIncentiveID, MarketingIncentiveMaster.RequestedDate, MarketingIncentiveMaster.PaymentPeriod, MarketingProgramMaster.Reference, MarketingProgramMaster.ReferenceCode, MarketingProgramMaster.MarketingProgramName, MarketingProgramMaster.MarketingProgramSubName, ListMarketingPaymentType.Description AS MarketingPaymentTypeDescription, MarketingIncentiveMaster.Remarks " + "\r\n";
            queryString = queryString + "       FROM        MarketingIncentiveMaster INNER JOIN " + "\r\n";
            queryString = queryString + "                   MarketingProgramMaster ON MarketingIncentiveMaster.MarketingProgramID = MarketingProgramMaster.MarketingProgramID INNER JOIN " + "\r\n";
            queryString = queryString + "                   ListMarketingPaymentType ON MarketingIncentiveMaster.MarketingPaymentTypeID = ListMarketingPaymentType.MarketingPaymentTypeID " + "\r\n";
            queryString = queryString + "       WHERE       MarketingIncentiveMaster.RequestedDate >= @LowerFillterDate AND MarketingIncentiveMaster.RequestedDate <= @UpperFillterDate " + "\r\n";

            SQLDatabase.CreateStoredProcedure("MarketingIncentiveListing", queryString);





            queryString = "     @MarketingIncentiveID Int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       SELECT      MarketingIncentiveID, MarketingProgramID, MarketingPaymentTypeID, RequestedDate, EntryDate, PaymentPeriod, PaymentMachanics, RequestedEmployeeID, NotedEmployeeID, ApprovedEmployeeID, UserID, UserOrganizationID, Remarks " + "\r\n";
            queryString = queryString + "       FROM        MarketingIncentiveMaster " + "\r\n";
            queryString = queryString + "       WHERE       MarketingIncentiveID = @MarketingIncentiveID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("MarketingIncentiveMasterSelect", queryString);


            queryString = "     @MarketingProgramID int, @MarketingPaymentTypeID int, @RequestedDate datetime, @EntryDate datetime, @PaymentPeriod nvarchar(100), @PaymentMachanics nvarchar(1000),	@RequestedEmployeeID int, @NotedEmployeeID int, @ApprovedEmployeeID int, @UserID int, @UserOrganizationID int, @Remarks nvarchar(100) " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       INSERT INTO     MarketingIncentiveMaster (MarketingProgramID, MarketingPaymentTypeID, RequestedDate, EntryDate, PaymentPeriod, PaymentMachanics, RequestedEmployeeID, NotedEmployeeID, ApprovedEmployeeID, UserID, UserOrganizationID, Remarks) VALUES (@MarketingProgramID, @MarketingPaymentTypeID, @RequestedDate, @EntryDate, @PaymentPeriod, @PaymentMachanics, @RequestedEmployeeID, @NotedEmployeeID, @ApprovedEmployeeID, @UserID, @UserOrganizationID, @Remarks) " + "\r\n";
            queryString = queryString + "       SELECT          MarketingIncentiveID, MarketingProgramID, MarketingPaymentTypeID, RequestedDate, EntryDate, PaymentPeriod, PaymentMachanics, RequestedEmployeeID, NotedEmployeeID, ApprovedEmployeeID, UserID, UserOrganizationID, Remarks FROM MarketingIncentiveMaster WHERE MarketingIncentiveID = SCOPE_IDENTITY() " + "\r\n";

            SQLDatabase.CreateStoredProcedure("MarketingIncentiveMasterInsert", queryString);


            queryString = " @MarketingIncentiveID int, @MarketingProgramID int, @MarketingPaymentTypeID int, @RequestedDate datetime, @EntryDate datetime, @PaymentPeriod nvarchar(100), @PaymentMachanics nvarchar(1000), @RequestedEmployeeID int, @NotedEmployeeID int, @ApprovedEmployeeID int, @UserID int, @UserOrganizationID int, @Remarks nvarchar(100) " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       UPDATE      MarketingIncentiveMaster SET MarketingProgramID = @MarketingProgramID, MarketingPaymentTypeID = @MarketingPaymentTypeID, RequestedDate = @RequestedDate, EntryDate = @EntryDate, PaymentPeriod = @PaymentPeriod, PaymentMachanics = @PaymentMachanics, RequestedEmployeeID = @RequestedEmployeeID, NotedEmployeeID = @NotedEmployeeID, ApprovedEmployeeID = @ApprovedEmployeeID, UserID = @UserID, UserOrganizationID = @UserOrganizationID, Remarks = @Remarks WHERE MarketingIncentiveID = @MarketingIncentiveID " + "\r\n";
            queryString = queryString + "       SELECT MarketingIncentiveID, MarketingProgramID, MarketingPaymentTypeID, RequestedDate, EntryDate, PaymentPeriod, PaymentMachanics, RequestedEmployeeID, NotedEmployeeID, ApprovedEmployeeID, UserID, UserOrganizationID, Remarks FROM MarketingIncentiveMaster WHERE MarketingIncentiveID = @MarketingIncentiveID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("MarketingIncentiveMasterUpdate", queryString);


            queryString = " @MarketingIncentiveID int ";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       DELETE FROM MarketingIncentiveMaster WHERE MarketingIncentiveID = @MarketingIncentiveID " + "\r\n";
            SQLDatabase.CreateStoredProcedure("MarketingIncentiveMasterDelete", queryString);







            queryString = " @MarketingIncentiveID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       SELECT      MarketingIncentiveID, SerialID, RequestedDate, PaymentPeriod, CustomerID, MarketingProgramID, MarketingPaymentTypeID, MarketingPaymentTermID, TurnOverAmount, IncentiveAmount, PaidAmount, Description, Remarks " + "\r\n";
            queryString = queryString + "       FROM        MarketingIncentiveDetail " + "\r\n";
            queryString = queryString + "       WHERE       MarketingIncentiveID = @MarketingIncentiveID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("MarketingIncentiveDetailSelect", queryString);


            queryString = " @MarketingIncentiveID int, @SerialID int, @RequestedDate datetime, @PaymentPeriod nvarchar(100), @CustomerID int, @MarketingProgramID int, @MarketingPaymentTypeID int, @MarketingPaymentTermID int, @TurnOverAmount float, @IncentiveAmount float, @PaidAmount float, @Description nvarchar(100), @Remarks nvarchar(100) " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       INSERT INTO MarketingIncentiveDetail (MarketingIncentiveID, SerialID, RequestedDate, PaymentPeriod, CustomerID, MarketingProgramID, MarketingPaymentTypeID, MarketingPaymentTermID, TurnOverAmount, IncentiveAmount, PaidAmount, Description, Remarks) VALUES (@MarketingIncentiveID, @SerialID, @RequestedDate, @PaymentPeriod, @CustomerID, @MarketingProgramID, @MarketingPaymentTypeID, @MarketingPaymentTermID, @TurnOverAmount, @IncentiveAmount, @PaidAmount, @Description, @Remarks) " + "\r\n";
            queryString = queryString + "       SELECT      MarketingIncentiveID, SerialID, RequestedDate, PaymentPeriod, CustomerID, MarketingProgramID, MarketingPaymentTypeID, MarketingPaymentTermID, TurnOverAmount, IncentiveAmount, PaidAmount, Description, Remarks FROM MarketingIncentiveDetail WHERE MarketingIncentiveID = @MarketingIncentiveID AND SerialID = @SerialID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("MarketingIncentiveDetailInsert", queryString);



            queryString = " @MarketingIncentiveID int, @SerialID int, @RequestedDate datetime, @PaymentPeriod nvarchar(100), @CustomerID int, @MarketingProgramID int, @MarketingPaymentTypeID int, @MarketingPaymentTermID int, @TurnOverAmount float, @IncentiveAmount float, @PaidAmount float, @Description nvarchar(100), @Remarks nvarchar(100) " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       UPDATE      MarketingIncentiveDetail SET RequestedDate = @RequestedDate, PaymentPeriod = @PaymentPeriod, CustomerID = @CustomerID, MarketingProgramID = @MarketingProgramID, MarketingPaymentTypeID = @MarketingPaymentTypeID, MarketingPaymentTermID = @MarketingPaymentTermID, TurnOverAmount = @TurnOverAmount, IncentiveAmount = @IncentiveAmount, PaidAmount = @PaidAmount, Description = @Description, Remarks = @Remarks WHERE MarketingIncentiveID = @MarketingIncentiveID AND SerialID = @SerialID " + "\r\n";
            queryString = queryString + "       SELECT      MarketingIncentiveID, SerialID, RequestedDate, PaymentPeriod, CustomerID, MarketingProgramID, MarketingPaymentTypeID, MarketingPaymentTermID, TurnOverAmount, IncentiveAmount, PaidAmount, Description, Remarks FROM MarketingIncentiveDetail WHERE MarketingIncentiveID = @MarketingIncentiveID AND SerialID = @SerialID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("MarketingIncentiveDetailUpdate", queryString);



            queryString = " @MarketingIncentiveID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       DELETE FROM MarketingIncentiveDetail WHERE MarketingIncentiveID = @MarketingIncentiveID " + "\r\n";
            SQLDatabase.CreateStoredProcedure("MarketingIncentiveDetailDelete", queryString);




            queryString = " @MarketingIncentiveID int, @SerialID int, @TurnOverAmount float, @IncentiveAmount float, @Description nvarchar(100), @Remarks nvarchar(100), @Original_RequestedDate date, @Original_MarketingProgramID int, @Original_MarketingPaymentTypeID int, @Original_CustomerID int, @Original_MarketingPaymentTermID int, @Original_PaymentPeriod nvarchar(100), @Original_TurnOverAmount float, @Original_IncentiveAmount float, @Original_Description nvarchar(100), @Original_Remarks nvarchar(100) " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       UPDATE      MarketingIncentiveDetail SET TurnOverAmount = @TurnOverAmount, IncentiveAmount = @IncentiveAmount, Description = @Description, Remarks = @Remarks WHERE MarketingIncentiveID = @MarketingIncentiveID AND SerialID = @SerialID AND RequestedDate = @Original_RequestedDate AND MarketingProgramID = @Original_MarketingProgramID AND MarketingPaymentTypeID = @Original_MarketingPaymentTypeID AND CustomerID = @Original_CustomerID AND MarketingPaymentTermID = @Original_MarketingPaymentTermID AND PaymentPeriod = @Original_PaymentPeriod AND TurnOverAmount = @Original_TurnOverAmount AND IncentiveAmount = @Original_IncentiveAmount AND @IncentiveAmount >= PaidAmount AND Description = @Original_Description AND Remarks = @Original_Remarks " + "\r\n";
            queryString = queryString + "       SELECT      MarketingIncentiveID, SerialID, RequestedDate, MarketingProgramID, MarketingPaymentTypeID, CustomerID, MarketingPaymentTermID, PaymentPeriod, TurnOverAmount, IncentiveAmount, PaidAmount, Description, Remarks FROM MarketingIncentiveDetail WHERE MarketingIncentiveID = @MarketingIncentiveID AND SerialID = @SerialID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("MarketingIncentiveDetailCustomUpdate", queryString);


            /// <summary>
            /// Check for editable
            /// </summary>
            queryArray = new string[1];
            queryArray[0] = " SELECT TOP 1 MarketingIncentiveID FROM MarketingIncentiveMaster WHERE MarketingIncentiveID = @FindIdentityID AND Approved = 1 ";

            SQLDatabase.CreateProcedureToCheckExisting("SPMarketingIncentiveApproved", queryArray);



            /// <summary>
            /// Check for editable
            /// </summary>
            queryArray = new string[1];
            queryArray[0] = " SELECT TOP 1 MarketingIncentiveID FROM MarketingPaymentMaster WHERE MarketingIncentiveID = @FindIdentityID ";

            SQLDatabase.CreateProcedureToCheckExisting("SPMarketingIncentiveEditable", queryArray);
        }




    }
}
